# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：get_stock_changes.py
#日期：2020-05-29
#备注：获取东方财富网的股票异动信息（火箭发射、有大买盘、封涨停板、打开跌停板）
CREATE TABLE `stock_changes` (
  `ch_type` varchar(20) DEFAULT NULL,
  `ch_remark` varchar(20) NOT NULL,
  `ch_time` varchar(30) NOT NULL,
  `ch_stock_no` varchar(10) NOT NULL,
  `ch_stock_name` varchar(10) NOT NULL,
  `ch_value` float DEFAULT NULL,
  PRIMARY KEY (`ch_stock_no`,`ch_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `stock_changes_report` (
  `report_date` varchar(32) NOT NULL COMMENT '统计日期',
  `report_num` int(10) NOT NULL COMMENT '统计数量',
  `report_type` varchar(32) NOT NULL COMMENT '统计类型',
  `report_remark` varchar(32) NOT NULL COMMENT '统计备注',
  `report_point` varchar(32) NOT NULL COMMENT '统计时间点',  
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`report_date`,`report_type`,report_point)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='每日股票异动统计信息';
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
import requests
import time
from pycacho.cachobase.deal_float import float_deal
from pacong.stock.stock_config import db,cursor,headers
from pycacho.cachobase.chinese_workday import get_trade_day
from pacong.stock.stock_base import get_stock_no
td_day = get_trade_day(0,'t')

# 获取股票异动信息
def get_stock(ch_type,date,ch_remark):
    url = 'http://push2ex.eastmoney.com/getAllStockChanges?type={0}&pageindex=0&pagesize=3000&ut=7eea3edcaed734bea9cbfc24409ed989&date={1}&dpt=wzchanges'.format(ch_type,date)
    print(url)
    resp = requests.get(url, headers=headers)
    data = resp.json()['data']['allstock']
    print('总异动数量：',resp.json()['data']['tc'])
    for stock in data:
        #print(stock)
        time = str(stock['tm'])
        if len(time)==6:
            c_time = time
        else:
            c_time = '0' + time
        ch_time = date + ' ' + c_time[0:2] + ':' + c_time[2:4] + ':' + c_time[4:6]   # td_day + ' ' +
        ch_stock_no = stock['c']
        ch_stock_name = stock['n']
        if ch_type == '4':
            ch_value = stock['i'].replace('0000','')
        else:
            ch_value = str(float_deal(float(stock['i'])*100) )
        print(ch_remark + '-->' + ch_time+ '-->' + ch_stock_no+ '-->' + ch_stock_name+ '-->' + ch_value)
        stock_changes_to_mysql(ch_type, ch_remark, ch_time, ch_stock_no, ch_stock_name, ch_value)

# 将股票异动信息存储（异动类型，）
def stock_changes_to_mysql(ch_type,ch_remark,ch_time,ch_stock_no,ch_stock_name,ch_value):
    sql = "insert into stock_changes (ch_type,ch_remark,ch_time,ch_stock_no,ch_stock_name,ch_value) values ('%s','%s','%s','%s','%s','%s');" \
          % (ch_type,ch_remark,ch_time,ch_stock_no,ch_stock_name,ch_value)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()

def merge(td_time):
    stock_type = {'60':'上证股票','68':'科创板股票','00':'深证股票','300':'创业板股票'}
    r_time = ['09','10','11','13','14','total']
    for key in stock_type:
        for r in r_time:
            r_point = td_time + ' ' + r
            sq = 'delete from stock_changes_report where report_date="%s" and report_type="%s" and report_point="%s" '%(td_time,key,r)
            if r=='total':
                sql = 'insert into stock_changes_report(report_date,report_num,report_type,report_remark,report_point,create_time) ' \
                  'select "%s",count(*),"%s","%s","%s",now() from stock_changes where SUBSTR(ch_time,1,10)="%s" and SUBSTR(ch_stock_no,1,"%s")="%s";'\
                  %(td_time,key,stock_type[key],r,td_time,len(key),key)
            else:
                sql = 'insert into stock_changes_report(report_date,report_num,report_type,report_remark,report_point,create_time) ' \
                      'select "%s",count(*),"%s","%s","%s",now() from stock_changes where SUBSTR(ch_time,1,13)="%s" and SUBSTR(ch_stock_no,1,"%s")="%s";' \
                      % (td_time, key, stock_type[key], r, r_point, len(key), key)
            print(sql)
            cursor.execute(sq)
            cursor.execute(sql)
            db.commit()

def exist_merge():
    for i in range(15):
        merge(get_trade_day(i, 't'))

# 获取具体股票异动信息
def get_stock_chg(stock_no,ch_stock_name,market,date,ch_type,ch_remark,ch_date):
    url = 'http://push2ex.eastmoney.com/getStockChanges?code={0}&market={1}&date={2}&ut=7eea3edcaed734bea9cbfc24409ed989&dpt=wzchanges'.format(stock_no,market,date)
    print(url)
    resp = requests.get(url, headers=headers)
    try:
        data = resp.json()['data']['data']
        for info in data:
            if str(info['t']) == ch_type:
                time = str(info['tm'])
                if len(time) == 6:
                    c_time = time
                else:
                    c_time = '0' + time
                ch_time = ch_date + ' ' + c_time[0:2] + ':' + c_time[2:4] + ':' + c_time[4:6]  # td_day + ' ' +
                ch_stock_no = stock_no
                ch_stock_name = ch_stock_name
                ch_value = info['u']
                print(ch_remark + '-->' + ch_time + '-->' + ch_stock_no + '-->' + ch_stock_name + '-->' + ch_value)
                stock_changes_to_mysql(ch_type, ch_remark, ch_time, ch_stock_no, ch_stock_name, ch_value)
    except:
        pass

# 7天内丢失的数据
def miss_data():
    for i in range(1,2):
        date = get_trade_day(i,'s')
        ch_date = get_trade_day(i,'t')
        #for stock in get_stock_no():
        for i in range(len(get_stock_no())):
            n = get_stock_no().index(['0','000001','平安银行'])  # 元素位置
            if i >= n:
                ch_stock_no = get_stock_no()[i][1]
                ch_stock_name = get_stock_no()[i][2]
                market = get_stock_no()[i][0]
                get_stock_chg(ch_stock_no,ch_stock_name, market,date, '8201','火箭发射',ch_date)


if __name__ == '__main__':
        #get_stock('4','封涨停板')            # 封涨停板
        #exist_merge()
        #miss_data()
        get_stock('8201', td_day, '火箭发射')
        #get_stock_chg('300112', '万讯自控',get_trade_day(0,'s'), '8201','火箭发射',get_trade_day(0,'t'))
        #merge(get_trade_day(0, 't'))
        #time.sleep(2)

